package business.knBase


import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.TreeSet;
import groovy.sql.DataSet
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import org.apache.log4j.Logger;
import java.sql.ResultSet
import java.util.concurrent.ConcurrentHashMap.Values;
import java.util.regex.Pattern;

/**
 * Se encarga de preparar las tablas de la base de datos de conocimiento
 * @author 53385255R
 *
 */
class PrepareKnBase {
	

	static Logger _log
	
	
		
	static main(args) {
		
		PrepareKnBase.fillSectorsFromCSV()
		
	}
		
	/**
	 * Inserta las palabras de las provincias
	 */
	static  void fillProvsFromCSV() {
		_log = Logger.getLogger("crear tabla provincias en knbase");
	
		
		business.database.Database.getInstance().sqlKnBase.execute("create table IF NOT EXISTS provWord (word text, prov text);")
		business.database.Database.getInstance().sqlKnBase.execute("create table IF NOT EXISTS provWord2 (word1 text, word2 text, prov text);")
		business.database.Database.getInstance().sqlKnBase.execute("create table IF NOT EXISTS provWord3 (word1 text, word2 text, word3 text, prov text);")
		//Recorremos los ficheros de la carpeta
	/*	println("Inserta el nombre de la carpeta: ");
		InputStreamReader converter = new InputStreamReader(System.in);
		BufferedReader in = new BufferedReader(converter);
*/		String path = "cities.csv"//in.readLine();
		
		//new File(path).eachFile() { file->
			File file = new File(path)
			println file.getName()
			file.splitEachLine(";") {fields ->
				String text = fields[0]
				String prov = fields[1]
				
				String[] spls = text.split(" ")
				
				if (spls.size() == 1) {
					String txt = spls[0].replaceAll(" ", "")
					business.database.Database.getInstance().sqlKnBase.execute("INSERT INTO provWord VALUES (${txt}, ${prov})")
				} else if (spls.size() == 2) {
				String txt1 = spls[0].replaceAll(" ", "")
				String txt2 = spls[1].replaceAll(" ", "")
				business.database.Database.getInstance().sqlKnBase.execute("INSERT INTO provWord2 VALUES (${txt1}, ${txt2},${prov})")
				} else if (spls.size() == 3) {
				String txt1 = spls[0].replaceAll(" ", "")
				String txt2 = spls[1].replaceAll(" ", "")
				String txt3 = spls[2].replaceAll(" ", "")
				business.database.Database.getInstance().sqlKnBase.execute("INSERT INTO provWord3 VALUES (${txt1}, ${txt2}, ${txt3},${prov})")
				} else {
				
				String txt1 = spls[0].replaceAll(" ", "")
				String txt2 = spls[1].replaceAll(" ", "")
				String txt3 = spls[3].replaceAll(" ", "")
				business.database.Database.getInstance().sqlKnBase.execute("INSERT INTO provWord3 VALUES (${txt1}, ${txt2}, ${txt3},${prov})")
				}

			}
	
		
	}
		
		
	/**
	* Actualiza la tabla de sectores
	*/
	static void fillSectorsFromCSV() {
	
		_log = Logger.getLogger("creamos tabla sectores en knbase");
		
		business.database.Database.getInstance().sqlKnBase.execute("create table IF NOT EXISTS  sector (instit varchar(4), sector varchar(4))")
		
		String path = "sectores.csv"
		
	   new File(path).splitEachLine(",") {fields ->
		   
		   String instit = fields[0]
		   String sect = fields[1]
		   
		   business.database.Database.getInstance().sqlKnBase.execute("Insert into sector (instit, sector) values ('${instit}', '${sect}');")
		   
	   }
	
	}


}
